https://postgrespro.ru/docs/postgrespro/12/performance-tips

Глава 
# 14. Оптимизация производительности
Содержание

    14.1. Использование EXPLAIN
    14.1.1. Азы EXPLAIN
    14.1.2. EXPLAIN ANALYZE
    14.1.3. Ограничения
    14.2. Статистика, используемая планировщиком
    14.2.1. Статистика по одному столбцу
    14.2.2. Расширенная статистика
    14.3. Управление планировщиком с помощью явных предложений JOIN
    14.4. Наполнение базы данных
    14.4.1. Отключите автофиксацию транзакций
    14.4.2. Используйте COPY
    14.4.3. Удалите индексы
    14.4.4. Удалите ограничения внешних ключей
    14.4.5. Увеличьте maintenance_work_mem
    14.4.6. Увеличьте max_wal_size
    14.4.7. Отключите архивацию WAL и потоковую репликацию
    14.4.8. Выполните в конце ANALYZE
    14.4.9. Несколько замечаний относительно pg_dump
    14.5. Оптимизация, угрожающая стабильности

Быстродействие запросов зависит от многих факторов. На некоторые из них могут воздействовать пользователи, а другие являются фундаментальными особенностями системы. В этой главе приводятся полезные советы, которые помогут понять их и оптимизировать производительность Postgres Pro.

## 14.1. Использование EXPLAIN
14.1.1. Азы EXPLAIN
14.1.2. EXPLAIN ANALYZE
14.1.3. Ограничения
Выполняя любой полученный запрос, Postgres Pro разрабатывает для него план запроса. Выбор правильного плана, соответствующего структуре запроса и характеристикам данным, крайне важен для хорошей производительности, поэтому в системе работает сложный планировщик, задача которого — подобрать хороший план. Узнать, какой план был выбран для какого-либо запроса, можно с помощью команды EXPLAIN. Понимание плана — это искусство, и чтобы овладеть им, нужен определённый опыт, но этот раздел расскажет о самых простых вещах.

Приведённые ниже примеры показаны на тестовой базе данных, которая создаётся для выявления регрессий в исходных кодах PostgreSQL текущей версии. Для неё предварительно выполняется VACUUM ANALYZE. Вы должны получить похожие результаты, если возьмёте ту же базу данных и проделаете следующие действия, но примерная стоимость и ожидаемое число строк у вас может немного отличаться из-за того, что статистика команды ANALYZE рассчитывается по случайной выборке, а оценки стоимости зависят от конкретной платформы.

В этих примерах используется текстовый формат вывода EXPLAIN, принятый по умолчанию, как более компактный и удобный для восприятия человеком. Если вывод EXPLAIN нужно передать какой-либо программе для дальнейшего анализа, лучше использовать один из машинно-ориентированных форматов (XML, JSON или YAML).

### 14.1.1. Азы EXPLAIN
Структура плана запроса представляет собой дерево узлов плана. Узлы на нижнем уровне дерева — это узлы сканирования, которые возвращают необработанные данные таблицы. Разным типам доступа к таблице соответствуют разные узлы: последовательное сканирование, сканирование индекса и сканирование битовой карты. Источниками строк могут быть не только таблицы, но и например, предложения VALUES и функции, возвращающие множества во FROM, и они представляются отдельными типами узлов сканирования. Если запрос требует объединения, агрегатных вычислений, сортировки или других операций с исходными строками, над узлами сканирования появляются узлы, обозначающие эти операции. И так как обычно операции могут выполняться разными способами, на этом уровне тоже могут быть узлы разных типов. В выводе команды EXPLAIN для каждого узла в дереве плана отводится одна строка, где показывается базовый тип узла плюс оценка стоимости выполнения данного узла, которую сделал для него планировщик. Если для узла выводятся дополнительные свойства, в вывод могут добавляться дополнительные строки, с отступом от основной информации узла. В самой первой строке (основной строке самого верхнего узла) выводится общая стоимость выполнения для всего плана; именно это значение планировщик старается минимизировать.

Взгляните на следующий простейший пример, просто иллюстрирующий формат вывода:

    EXPLAIN SELECT * FROM tenk1;

                            QUERY PLAN
    -------------------------------------------------------------
    Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)
Этот запрос не содержит предложения WHERE, поэтому он должен просканировать все строки таблицы, так что планировщик выбрал план простого последовательного сканирования. Числа, перечисленные в скобках (слева направо), имеют следующий смысл:

Приблизительная стоимость запуска. Это время, которое проходит, прежде чем начнётся этап вывода данных, например для сортирующего узла это время сортировки.

Приблизительная общая стоимость. Она вычисляется в предположении, что узел плана выполняется до конца, то есть возвращает все доступные строки. На практике родительский узел может досрочно прекратить чтение строк дочернего (см. приведённый ниже пример с LIMIT).

Ожидаемое число строк, которое должен вывести этот узел плана. При этом так же предполагается, что узел выполняется до конца.

Ожидаемый средний размер строк, выводимых этим узлом плана (в байтах).

Стоимость может измеряться в произвольных единицах, определяемых параметрами планировщика (см. Подраздел 18.7.2). Традиционно единицей стоимости считается операция чтения страницы с диска; то есть seq_page_cost обычно равен 1.0, а другие параметры задаётся относительно него. Примеры в этом разделе выполняются со стандартными параметрами стоимости.

Важно понимать, что стоимость узла верхнего уровня включает стоимость всех его потомков. Также важно осознавать, что эта стоимость отражает только те факторы, которые учитывает планировщик. В частности, она не зависит от времени, необходимого для передачи результирующих строк клиенту, хотя оно может составлять значительную часть общего времени выполнения запроса. Тем не менее планировщик игнорирует эту величину, так как он всё равно не сможет изменить её, выбрав другой план. (Мы верим в то, что любой правильный план запроса выдаёт один и тот же набор строк.)

Значение rows здесь имеет особенность — оно выражает не число строк, обработанных или просканированных узлом плана, а число строк, выданных этим узлом. Часто оно окажется меньше числа просканированных строк в результате применённой к узлу фильтрации по условиям WHERE. В идеале, на верхнем уровне это значение будет приблизительно равно числу строк, которое фактически возвращает, изменяет или удаляет запрос.

Возвращаясь к нашему примеру:

    EXPLAIN SELECT * FROM tenk1;

                            QUERY PLAN
    -------------------------------------------------------------
    Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)
    Эти числа получаются очень просто. Выполните:

    SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
и вы увидите, что tenk1 содержит 358 страниц диска и 10000 строк. Общая стоимость вычисляется как (число_чтений_диска * seq_page_cost) + (число_просканированных_строк * cpu_tuple_cost). По умолчанию, seq_page_cost равно 1.0, а cpu_tuple_cost — 0.01, так что приблизительная стоимость запроса равна (358 * 1.0) + (10000 * 0.01) = 458.

Теперь давайте изменим запрос, добавив в него предложение WHERE:

    EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

                                QUERY PLAN
    ------------------------------------------------------------
    Seq Scan on tenk1  (cost=0.00..483.00 rows=7001 width=244)
    Filter: (unique1 < 7000)
Заметьте, что в выводе EXPLAIN показано, что условие WHERE применено как «фильтр» к узлу плана Seq Scan (Последовательное сканирование). Это означает, что узел плана проверяет это условие для каждого просканированного им узла и выводит только те строки, которые удовлетворяют ему. Предложение WHERE повлияло на оценку числа выходных строк. Однако при сканировании потребуется прочитать все 10000 строк, поэтому общая стоимость не уменьшилась. На деле она даже немного увеличилась (на 10000 * cpu_operator_cost, если быть точными), отражая дополнительное время, которое потребуется процессору на проверку условия WHERE.

Фактическое число строк результата этого запроса будет равно 7000, но значение rows даёт только приблизительное значение. Если вы попытаетесь повторить этот эксперимент, вы можете получить немного другую оценку; более того, она может меняться после каждой команды ANALYZE, так как ANALYZE получает статистику по случайной выборке таблицы.

Теперь давайте сделаем ограничение более избирательным:

    EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                QUERY PLAN
    --------------------------------------------------------------------------
    Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)
    Recheck Cond: (unique1 < 100)
    ->  Bitmap Index Scan on tenk1_unique1
                                            (cost=0.00..5.01 rows=101 width=0)
            Index Cond: (unique1 < 100)
В данном случае планировщик решил использовать план из двух этапов: сначала дочерний узел плана просматривает индекс и находит в нём адреса строк, соответствующих условию индекса, а затем верхний узел собственно выбирает эти строки из таблицы. Выбирать строки по отдельности гораздо дороже, чем просто читать их последовательно, но так как читать придётся не все страницы таблицы, это всё равно будет дешевле, чем сканировать всю таблицу. (Использование двух уровней плана объясняется тем, что верхний узел сортирует адреса строк, выбранных из индекса, в физическом порядке, прежде чем читать, чтобы снизить стоимость отдельных чтений. Слово «bitmap» (битовая карта) в имени узла обозначает механизм, выполняющий сортировку.)

Теперь давайте добавим ещё одно условие в предложение WHERE:

    EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';

                                QUERY PLAN
    --------------------------------------------------------------------------
    Bitmap Heap Scan on tenk1  (cost=5.01..229.40 rows=1 width=244)
    Recheck Cond: (unique1 < 100)
    Filter: (stringu1 = 'xxx'::name)
    ->  Bitmap Index Scan on tenk1_unique1
                                            (cost=0.00..5.04 rows=101 width=0)
            Index Cond: (unique1 < 100)
Добавленное условие stringu1 = 'xxx' уменьшает оценку числа результирующих строк, но не стоимость запроса, так как просматриваться будет тот же набор строк, что и раньше. Заметьте, что условие на stringu1 не добавляется в качестве условия индекса, так как индекс построен только по столбцу unique1. Вместо этого оно применяется как фильтр к строкам, полученным по индексу. В результате стоимость даже немного увеличилась, отражая добавление этой проверки.

В некоторых случаях планировщик предпочтёт «простой» план сканирования индекса:

    EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;

                                QUERY PLAN
    ---------------------------------------------------------------------------
    Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)
    Index Cond: (unique1 = 42)
В плане такого типа строки таблицы выбираются в порядке индекса, в результате чего чтение их обходится дороже, но так как их немного, дополнительно сортировать положения строк не стоит. Вы часто будете встречать этот тип плана в запросах, которые выбирают всего одну строку. Также он часто задействуется там, где условие ORDER BY соответствует порядку индекса, так как в этих случаях для выполнения ORDER BY не требуется дополнительный шаг сортировки.

Если в таблице есть отдельные индексы по разным столбцам, фигурирующим в WHERE, планировщик может выбрать сочетание этих индексов (с AND и OR):

    EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

                                        QUERY PLAN
    -------------------------------------------------------------------------------------
    Bitmap Heap Scan on tenk1  (cost=25.08..60.21 rows=10 width=244)
    Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
    ->  BitmapAnd  (cost=25.08..25.08 rows=10 width=0)
            ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)
                Index Cond: (unique1 < 100)
            ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..19.78 rows=999 width=0)
                Index Cond: (unique2 > 9000)
Но для этого потребуется обойти оба индекса, так что это не обязательно будет выгоднее, чем просто просмотреть один индекс, а второе условие обработать как фильтр. Измените диапазон и вы увидите, как это повлияет на план.

Следующий пример иллюстрирует эффекты LIMIT:

    EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;

                                        QUERY PLAN
    -------------------------------------------------------------------------------------
    Limit  (cost=0.29..14.48 rows=2 width=244)
    ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..71.27 rows=10 width=244)
            Index Cond: (unique2 > 9000)
            Filter: (unique1 < 100)
Это тот же запрос, что и раньше, но добавили мы в него LIMIT, чтобы возвращались не все строки, и планировщик решает выполнять запрос по-другому. Заметьте, что общая стоимость и число строк для узла Index Scan рассчитываются в предположении, что он будет выполняться полностью. Однако узел Limit должен остановиться, получив только пятую часть всех строк, так что его стоимость будет составлять одну пятую от вычисленной ранее, и это и будет итоговой оценкой стоимости запроса. С другой стороны, планировщик мог бы просто добавить в предыдущий план узел Limit, но это не избавило бы от затрат на запуск сканирования битовой карты, а значит, общая стоимость была бы выше 25 единиц.

Давайте попробуем соединить две таблицы по столбцам, которые мы уже использовали:

    EXPLAIN SELECT *
    FROM tenk1 t1, tenk2 t2
    WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

                                        QUERY PLAN
    --------------------------------------------------------------------------------------
    Nested Loop  (cost=4.65..118.62 rows=10 width=488)
    ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)
            Recheck Cond: (unique1 < 10)
            ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
                Index Cond: (unique1 < 10)
    ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.91 rows=1 width=244)
            Index Cond: (unique2 = t1.unique2)
В этом плане появляется узел соединения с вложенным циклом, на вход которому поступают данные от двух его потомков, узлов сканирования. Эту структуру плана отражает отступ основных строк его узлов. Первый, или «внешний», потомок соединения — узел сканирования битовой карты, похожий на те, что мы видели раньше. Его стоимость и число строк те же, что мы получили бы для запроса SELECT ... WHERE unique1 < 10, так как к этому узлу добавлено предложение WHERE unique1 < 10. Условие t1.unique2 = t2.unique2 ещё не учитывается, поэтому оно не влияет на число строк узла внешнего сканирования. Узел соединения с вложенным циклом будет выполнять узел «внутреннего» потомка для каждой строки, полученной из внешнего потомка. Значения столбцов из текущей внешней строки могут использоваться во внутреннем сканировании (в данном случае это значение t1.unique2), поэтому мы получаем план и стоимость примерно такие, как и раньше для простого запроса SELECT ... WHERE t2.unique2 = константа. (На самом деле оценочная стоимость немного меньше, в предположении, что при неоднократном сканировании индекса по t2 положительную роль сыграет кеширование.) В результате стоимость узла цикла складывается из стоимости внешнего сканирования, цены внутреннего сканирования, умноженной на число строк (здесь 10 * 7.91), и небольшой наценки за обработку соединения.

В этом примере число выходных строк соединения равно произведению чисел строк двух узлов сканирования, но это не всегда будет так, потому что в дополнительных условиях WHERE могут упоминаться обе таблицы, так что применить их можно будет только в точке соединения, а не в одном из узлов сканирования. Например:

    EXPLAIN SELECT *
    FROM tenk1 t1, tenk2 t2
    WHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;

                                            QUERY PLAN
    ---------------------------------------------------------------------------------------------
    Nested Loop  (cost=4.65..49.46 rows=33 width=488)
    Join Filter: (t1.hundred < t2.hundred)
    ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)
            Recheck Cond: (unique1 < 10)
            ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
                Index Cond: (unique1 < 10)
    ->  Materialize  (cost=0.29..8.51 rows=10 width=244)
            ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)
                Index Cond: (unique2 < 10)
Условие t1.hundred < t2.hundred не может быть проверено в индексе tenk2_unique2, поэтому оно применяется в узле соединения. Это уменьшает оценку числа выходных строк, тогда как число строк в узлах сканирования не меняется.

Заметьте, что здесь планировщик решил «материализовать» внутреннее отношение соединения, поместив поверх него узел плана Materialize (Материализовать). Это значит, что сканирование индекса t2 будет выполняться только единожды, при том, что узлу вложенного цикла соединения потребуется прочитать данные десять раз, по числу строк во внешнем соединении. Узел Materialize сохраняет считанные данные в памяти, чтобы затем выдать их из памяти на следующих проходах.

Выполняя внешние соединения, вы можете встретить узлы плана с присоединёнными условиями, как обычными «Filter», так и «Join Filter» (Фильтр соединения). Условия Join Filter формируются из предложения ON для внешнего соединения, так что если строка не удовлетворяет условию Join Filter, она всё же выдаётся как строка, дополненная значениями NULL. Обычное же условие Filter применяется после правил внешнего соединения и поэтому полностью исключает строки. Во внутреннем соединении оба этих фильтра работают одинаково.

Если немного изменить избирательность запроса, мы можем получить совсем другой план соединения:

    EXPLAIN SELECT *
    FROM tenk1 t1, tenk2 t2
    WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                            QUERY PLAN
    ------------------------------------------------------------------------------------------
    Hash Join  (cost=230.47..713.98 rows=101 width=488)
    Hash Cond: (t2.unique2 = t1.unique2)
    ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)
    ->  Hash  (cost=229.20..229.20 rows=101 width=244)
            ->  Bitmap Heap Scan on tenk1 t1  (cost=5.07..229.20 rows=101 width=244)
                Recheck Cond: (unique1 < 100)
                ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)
                        Index Cond: (unique1 < 100)
Здесь планировщик выбирает соединение по хешу, при котором строки одной таблицы записываются в хеш-таблицу в памяти, после чего сканируется другая таблица и для каждой её строки проверяется соответствие по хеш-таблице. Обратите внимание, что и здесь отступы отражают структуру плана: результат сканирования битовой карты по tenk1 подаётся на вход узлу Hash, который конструирует хеш-таблицу. Затем она передаётся узлу Hash Join, который читает строки из узла внешнего потомка и проверяет их по этой хеш-таблице.

Ещё один возможный тип соединения — соединение слиянием:

    EXPLAIN SELECT *
    FROM tenk1 t1, onek t2
    WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                            QUERY PLAN
    ------------------------------------------------------------------------------------------
    Merge Join  (cost=198.11..268.19 rows=10 width=488)
    Merge Cond: (t1.unique2 = t2.unique2)
    ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..656.28 rows=101 width=244)
            Filter: (unique1 < 100)
    ->  Sort  (cost=197.83..200.33 rows=1000 width=244)
            Sort Key: t2.unique2
            ->  Seq Scan on onek t2  (cost=0.00..148.00 rows=1000 width=244)
Соединение слиянием требует, чтобы входные данные для него были отсортированы по ключам соединения. В этом плане данные tenk1 сортируются после сканирования индекса, при котором все строки просматриваются в правильном порядке, но таблицу onek выгоднее оказывается последовательно просканировать и отсортировать, так как в этой таблице нужно обработать гораздо больше строк. (Последовательное сканирование и сортировка часто бывает быстрее сканирования индекса, когда нужно отсортировать много строк, так как при сканировании по индексу обращения к диску не упорядочены.)

Один из способов посмотреть различные планы — принудить планировщик не считать выбранную им стратегию самой выгодной, используя флаги, описанные в Подразделе 18.7.1. (Это полезный, хотя и грубый инструмент. См. также Раздел 14.3.) Например, если мы убеждены, что последовательное сканирование и сортировка — не лучший способ обработать таблицу onek в предыдущем примере, мы можем попробовать

    SET enable_sort = off;

    EXPLAIN SELECT *
    FROM tenk1 t1, onek t2
    WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                            QUERY PLAN
    ------------------------------------------------------------------------------------------
    Merge Join  (cost=0.56..292.65 rows=10 width=488)
    Merge Cond: (t1.unique2 = t2.unique2)
    ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..656.28 rows=101 width=244)
            Filter: (unique1 < 100)
    ->  Index Scan using onek_unique2 on onek t2  (cost=0.28..224.79 rows=1000 width=244)
Видно, что планировщик считает сортировку onek со сканированием индекса примерно на 12% дороже, чем последовательное сканирование и сортировку. Конечно, может возникнуть вопрос — а правильно ли это? Мы можем ответить на него, используя описанную ниже команду EXPLAIN ANALYZE.

### 14.1.2. EXPLAIN ANALYZE
Точность оценок планировщика можно проверить, используя команду EXPLAIN с параметром ANALYZE. С этим параметром EXPLAIN на самом деле выполняет запрос, а затем выводит фактическое число строк и время выполнения, накопленное в каждом узле плана, вместе с теми же оценками, что выдаёт обычная команда EXPLAIN. Например, мы можем получить примерно такой результат:

    EXPLAIN ANALYZE SELECT *
    FROM tenk1 t1, tenk2 t2
    WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

                                                            QUERY PLAN
    ---------------------------------------------------------------------------------------------------------------------------------
    Nested Loop  (cost=4.65..118.62 rows=10 width=488) (actual time=0.128..0.377 rows=10 loops=1)
    ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244) (actual time=0.057..0.121 rows=10 loops=1)
            Recheck Cond: (unique1 < 10)
            ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0) (actual time=0.024..0.024 rows=10 loops=1)
                Index Cond: (unique1 < 10)
    ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.91 rows=1 width=244) (actual time=0.021..0.022 rows=1 loops=10)
            Index Cond: (unique2 = t1.unique2)
    Planning time: 0.181 ms
    Execution time: 0.501 ms
Заметьте, что значения «actual time» (фактическое время) приводятся в миллисекундах, тогда как оценки cost (стоимость) выражаются в произвольных единицах, так что они вряд ли совпадут. Обычно важнее определить, насколько приблизительная оценка числа строк близка к действительности. В этом примере они в точности совпали, но на практике так бывает редко.

В некоторых планах запросов некоторый внутренний узел может выполняться неоднократно. Например, внутреннее сканирование индекса будет выполняться для каждой внешней строки во вложенном цикле верхнего уровня. В таких случаях значение loops (циклы) показывает, сколько всего раз выполнялся этот узел, а фактическое время и число строк вычисляется как среднее по всем итерациям. Это делается для того, чтобы полученные значения можно было сравнить с выводимыми приблизительными оценками. Чтобы получить общее время, затраченное на выполнение узла, время одной итерации нужно умножить на значение loops. В показанном выше примере мы потратили в общей сложности 0.220 мс на сканирование индекса в tenk2.

В ряде случаев EXPLAIN ANALYZE выводит дополнительную статистику по выполнению, включающую не только время выполнения узлов и число строк. Для узлов Sort и Hash, например выводится следующая информация:

    EXPLAIN ANALYZE SELECT *
    FROM tenk1 t1, tenk2 t2
    WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;

                                                                    QUERY PLAN
    --------------------------------------------------------------------------------------------------------------------------------------------
    Sort  (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
    Sort Key: t1.fivethous
    Sort Method: quicksort  Memory: 77kB
    ->  Hash Join  (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
            Hash Cond: (t2.unique2 = t1.unique2)
            ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)
            ->  Hash  (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 28kB
                ->  Bitmap Heap Scan on tenk1 t1  (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
                        Recheck Cond: (unique1 < 100)
                        ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
                            Index Cond: (unique1 < 100)
    Planning time: 0.194 ms
    Execution time: 8.008 ms
Для узла Sort показывается использованный метод и место сортировки (в памяти или на диске), а также задействованный объём памяти. Для узла Hash выводится число групп и пакетов хеша, а также максимальный объём, который заняла в памяти хеш-таблица. (Если число пакетов больше одного, часть хеш-таблицы будет выгружаться на диск и занимать какое-то пространство, но его объём здесь не показывается.)

Другая полезная дополнительная информация — число строк, удалённых условием фильтра:

    EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;

                                                QUERY PLAN
    ---------------------------------------------------------------------------------------------------------
    Seq Scan on tenk1  (cost=0.00..483.00 rows=7000 width=244) (actual time=0.016..5.107 rows=7000 loops=1)
    Filter: (ten < 7)
    Rows Removed by Filter: 3000
    Planning time: 0.083 ms
    Execution time: 5.905 ms
Эти значения могут быть особенно ценны для условий фильтра, применённых к узлам соединения. Строка «Rows Removed» выводится, только когда условие фильтра отбрасывает минимум одну просканированную строку или потенциальную пару соединения, если это узел соединения.

Похожую ситуацию можно наблюдать при сканировании «неточного» индекса. Например, рассмотрим этот план поиска многоугольников, содержащих указанную точку:

    EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';

                                                QUERY PLAN
    ------------------------------------------------------------------------------------------------------
    Seq Scan on polygon_tbl  (cost=0.00..1.05 rows=1 width=32) (actual time=0.044..0.044 rows=0 loops=1)
    Filter: (f1 @> '((0.5,2))'::polygon)
    Rows Removed by Filter: 4
    Planning time: 0.040 ms
    Execution time: 0.083 ms
Планировщик полагает (и вполне справедливо), что таблица слишком мала для сканирования по индексу, поэтому он выбирает последовательное сканирование, при котором все строки отбрасываются условием фильтра. Но если мы принудим его выбрать сканирование по индексу, мы получим:

    SET enable_seqscan TO off;

    EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';

                                                            QUERY PLAN
    --------------------------------------------------------------------------------------------------------------------------
    Index Scan using gpolygonind on polygon_tbl  (cost=0.13..8.15 rows=1 width=32) (actual time=0.062..0.062 rows=0 loops=1)
    Index Cond: (f1 @> '((0.5,2))'::polygon)
    Rows Removed by Index Recheck: 1
    Planning time: 0.034 ms
    Execution time: 0.144 ms
Здесь мы видим, что индекс вернул одну потенциально подходящую строку, но затем она была отброшена при перепроверке условия индекса. Это объясняется тем, что индекс GiST является «неточным» для проверок включений многоугольников: фактически он возвращает строки с многоугольниками, перекрывающими точку по координатам, а затем для этих строк нужно выполнять точную проверку.

EXPLAIN принимает параметр BUFFERS (который также можно применять с ANALYZE), включающий ещё более подробную статистику выполнения запроса:

    EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

                                                            QUERY PLAN
    ---------------------------------------------------------------------------------------------------------------------------------
    Bitmap Heap Scan on tenk1  (cost=25.08..60.21 rows=10 width=244) (actual time=0.323..0.342 rows=10 loops=1)
    Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
    Buffers: shared hit=15
    ->  BitmapAnd  (cost=25.08..25.08 rows=10 width=0) (actual time=0.309..0.309 rows=0 loops=1)
            Buffers: shared hit=7
            ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)
                Index Cond: (unique1 < 100)
                Buffers: shared hit=2
            ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..19.78 rows=999 width=0) (actual time=0.227..0.227 rows=999 loops=1)
                Index Cond: (unique2 > 9000)
                Buffers: shared hit=5
    Planning time: 0.088 ms
    Execution time: 0.423 ms
Значения, которые выводятся с параметром BUFFERS, помогают понять, на какие части запроса приходится большинство операций ввода-вывода.

Не забывайте, что EXPLAIN ANALYZE действительно выполняет запрос, хотя его результаты могут не показываться, а заменяться выводом команды EXPLAIN. Поэтому при таком анализе возможны побочные эффекты. Если вы хотите проанализировать запрос, изменяющий данные, но при этом сохранить прежние данные таблицы, вы можете откатить транзакцию после запроса:

    BEGIN;

    EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;

                                                            QUERY PLAN
    --------------------------------------------------------------------------------------------------------------------------------
    Update on tenk1  (cost=5.07..229.46 rows=101 width=250) (actual time=14.628..14.628 rows=0 loops=1)
    ->  Bitmap Heap Scan on tenk1  (cost=5.07..229.46 rows=101 width=250) (actual time=0.101..0.439 rows=100 loops=1)
            Recheck Cond: (unique1 < 100)
            ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)
                Index Cond: (unique1 < 100)
    Planning time: 0.079 ms
    Execution time: 14.727 ms

    ROLLBACK;
Как показано в этом примере, когда выполняется команда INSERT, UPDATE или DELETE, собственно изменение данных в таблице происходит в узле верхнего уровня Insert, Update или Delete. Узлы плана более низких уровней выполняют работу по нахождению старых строк и/или вычислению новых данных. Поэтому вверху мы видим тот же тип сканирования битовой карты, что и раньше, только теперь его вывод подаётся узлу Update, который сохраняет изменённые строки. Стоит отметить, что узел, изменяющий данные, может выполняться значительное время (в данном случае это составляет львиную часть всего времени), но планировщик не учитывает эту работу в оценке общей стоимости. Это связано с тем, что эта работа будет одинаковой при любом правильном плане запроса, и поэтому на выбор плана она не влияет.

Когда команда UPDATE или DELETE имеет дело с иерархией наследования, вывод может быть таким:

    EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101;
                                        QUERY PLAN
    -----------------------------------------------------------------------------------
    Update on parent  (cost=0.00..24.53 rows=4 width=14)
    Update on parent
    Update on child1
    Update on child2
    Update on child3
    ->  Seq Scan on parent  (cost=0.00..0.00 rows=1 width=14)
            Filter: (f1 = 101)
    ->  Index Scan using child1_f1_key on child1  (cost=0.15..8.17 rows=1 width=14)
            Index Cond: (f1 = 101)
    ->  Index Scan using child2_f1_key on child2  (cost=0.15..8.17 rows=1 width=14)
            Index Cond: (f1 = 101)
    ->  Index Scan using child3_f1_key on child3  (cost=0.15..8.17 rows=1 width=14)
            Index Cond: (f1 = 101)
В этом примере узлу Update помимо изначально упомянутой в запросе родительской таблицы нужно обработать ещё три дочерние таблицы. Поэтому формируются четыре плана сканирования, по одному для каждой таблицы. Ясности ради для узла Update добавляется примечание, показывающее, какие именно таблицы будут изменяться, в том же порядке, в каком они идут в соответствующих внутренних планах. (Эти примечания появились в PostgreSQL 9.5; до этого о целевых таблицах приходилось догадываться, изучая внутренние планы узла.)

Под заголовком Planning time (Время планирования) команда EXPLAIN ANALYZE выводит время, затраченное на построение плана запроса из разобранного запроса и его оптимизацию. Время собственно разбора или перезаписи запроса в него не включается.

Значение Execution time (Время выполнения), выводимое командой EXPLAIN ANALYZE, включает продолжительность запуска и остановки исполнителя запроса, а также время выполнения всех сработавших триггеров, но не включает время разбора, перезаписи и планирования запроса. Время, потраченное на выполнение триггеров BEFORE (если такие имеются) включается во время соответствующих узлов Insert, Update или Delete node; но время выполнения триггеров AFTER не учитывается, так как триггеры AFTER срабатывают после выполнения всего плана. Общее время, проведённое в каждом триггере (BEFORE или AFTER), также выводится отдельно. Заметьте, что триггеры отложенных ограничений выполняются только в конце транзакции, так что время их выполнения EXPLAIN ANALYZE не учитывает.

### 14.1.3. Ограничения
Время выполнения, измеренное командой EXPLAIN ANALYZE, может значительно отличаться от времени выполнения того же запроса в обычном режиме. Тому есть две основных причины. Во-первых, так как при анализе никакие строки результата не передаются клиенту, время ввода/вывода и передачи по сети не учитывается. Во-вторых, может быть существенной дополнительная нагрузка, связанная с функциями измерений EXPLAIN ANALYZE, особенно в системах, где вызов gettimeofday() выполняется медленно. Для измерения этой нагрузки вы можете воспользоваться утилитой pg_test_timing.

Результаты EXPLAIN не следует распространять на ситуации, значительно отличающиеся от тех, в которых вы проводите тестирование. В частности, не следует полагать, что выводы, полученные для игрушечной таблицы, будут применимы и для настоящих больших таблиц. Оценки стоимости нелинейны и планировщик может выбирать разные планы в зависимости от размера таблицы. Например, в крайнем случае вся таблица может уместиться в одну страницу диска, и тогда вы почти наверняка получите план последовательного сканирования, независимо от того, есть у неё и индексы или нет. Планировщик понимает, что для обработки таблицы ему в любом случае потребуется прочитать одну страницу, так что нет никакого смысла обращаться к ещё одной странице за индексом. (Мы наблюдали это в показанном выше примере с polygon_tbl.)

Бывает, что фактическое и приближённо оценённое значения не совпадают, но в этом нет ничего плохого. Например, это возможно, когда выполнение плана узла прекращается преждевременно из-за указания LIMIT или подобного эффекта. Например, для запроса с LIMIT, который мы пробовали раньше:

    EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;

                                                            QUERY PLAN
    -------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=0.29..14.71 rows=2 width=244) (actual time=0.177..0.249 rows=2 loops=1)
    ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..72.42 rows=10 width=244) (actual time=0.174..0.244 rows=2 loops=1)
            Index Cond: (unique2 > 9000)
            Filter: (unique1 < 100)
            Rows Removed by Filter: 287
    Planning time: 0.096 ms
    Execution time: 0.336 ms
Оценки стоимости и числа строк для узла Index Scan показываются в предположении, что этот узел будет выполняться до конца. Но в действительности узел Limit прекратил запрашивать строки, как только получил первые две, так что фактическое число строк равно 2 и время выполнения запроса будет меньше, чем рассчитал планировщик. Но это не ошибка, а просто следствие того, что оценённые и фактические значения выводятся по-разному.

Соединения слиянием также имеют свои особенности, которые могут ввести в заблуждение. Соединение слиянием прекратит читать один источник данных, если второй будет прочитан до конца, а следующее значение ключа в первом больше последнего значения во втором. В этом случае пар строк больше не будет, так что сканировать первый источник дальше нет смысла. В результате будут прочитаны не все строки одного потомка и вы получите тот же эффект, что и с LIMIT. Кроме того, если внешний (первый) потомок содержит строки с повторяющимися значениями ключа, внутренний (второй) потомок сдвинется назад и повторно выдаст строки для этого значения ключа. EXPLAIN ANALYZE считает эти повторяющиеся строки, как если бы это действительно были дополнительные строки внутреннего источника. Когда во внешнем узле много таких повторений ключей, фактическое число строк, подсчитанное для внутреннего узла, может значительно превышать число строк в соответствующей таблице.

Для узлов BitmapAnd (Логическое произведение битовых карт) и BitmapOr (Логическое сложение битовых карт) фактическое число строк всегда равно 0 из-за ограничений реализации.

Обычно EXPLAIN выводит подробности для каждого узла плана, сгенерированного планировщиком. Однако бывают ситуации, когда исполнитель может определить, что некоторые узлы не нужно выполнять, так как они не могут выдать никакие строки, с учётом значений параметров, ставших известными уже после планирования. (В настоящее время это может произойти только с дочерними узлами Append или MergeAppend, сканирующими секционированную таблицу.) В таких ситуациях эти узлы плана не попадают в вывод EXPLAIN, а в плане появляется запись Subplans Removed: N (Подпланов удалено: N).

## 14.2. Статистика, используемая планировщиком
14.2.1. Статистика по одному столбцу
14.2.2. Расширенная статистика
### 14.2.1. Статистика по одному столбцу
Как было показано в предыдущем разделе, планировщик запросов должен оценить число строк, возвращаемых запросов, чтобы сделать правильный выбор в отношении плана запроса. В этом разделе кратко описывается статистика, которую использует система для этих оценок.

В частности, статистика включает общее число записей в каждой таблице и индексе, а также число дисковых блоков, которые они занимают. Эта информация содержится в таблице pg_class, в столбцах reltuples и relpages. Получить её можно, например так:

    SELECT relname, relkind, reltuples, relpages
    FROM pg_class
    WHERE relname LIKE 'tenk1%';

        relname        | relkind | reltuples | relpages
    ----------------------+---------+-----------+----------
    tenk1                | r       |     10000 |      358
    tenk1_hundred        | i       |     10000 |       30
    tenk1_thous_tenthous | i       |     10000 |       30
    tenk1_unique1        | i       |     10000 |       30
    tenk1_unique2        | i       |     10000 |       30
    (5 rows)
Здесь мы видим, что tenk1 содержит 10000 строк данных и столько же строк в индексах (что неудивительно), но объём индексов гораздо меньше таблицы.

Для большей эффективности reltuples и relpages не пересчитываются «на лету», так что они обычно содержат несколько устаревшие значения. Их обновляют команды VACUUM, ANALYZE и несколько команд DDL, такие как CREATE INDEX. VACUUM и ANALYZE могут не сканировать всю таблицу (и обычно так и делают), а только вычислить приращение reltuples по части таблицы, так что результат остаётся приблизительным. В любом случае планировщик пересчитывает значения, полученные из pg_class, в пропорции к текущему физическому размеру таблицы и таким образом уточняет приближение.

Большинство запросов возвращают не все строки таблицы, а только немногие из них, ограниченные условиями WHERE. Поэтому планировщику нужно оценить избирательность условий WHERE, то есть определить, какой процент строк будет соответствовать каждому условию в предложении WHERE. Нужная для этого информация хранится в системном каталоге pg_statistic. Значения в pg_statistic обновляются командами ANALYZE и VACUUM ANALYZE и никогда не бывают точными, даже сразу после обновления.

Для исследования статистики лучше обращаться не непосредственно к таблице pg_statistic, а к представлению pg_stats, предназначенному для облегчения восприятия этой информации. Кроме того, представление pg_stats доступно для чтения всем, тогда как pg_statistic — только суперпользователям. (Это сделано для того, чтобы непривилегированные пользователи не могли ничего узнать о содержимом таблиц других людей из статистики. Представление pg_stats устроено так, что оно показывает строки только для тех таблиц, которые может читать данный пользователь.) Например, мы можем выполнить:

    SELECT attname, inherited, n_distinct,
        array_to_string(most_common_vals, E'\n') as most_common_vals
    FROM pg_stats
    WHERE tablename = 'road';

    attname | inherited | n_distinct |          most_common_vals
    ---------+-----------+------------+------------------------------------
    name    | f         |  -0.363388 | I- 580                        Ramp+
            |           |            | I- 880                        Ramp+
            |           |            | Sp Railroad                       +
            |           |            | I- 580                            +
            |           |            | I- 680                        Ramp
    name    | t         |  -0.284859 | I- 880                        Ramp+
            |           |            | I- 580                        Ramp+
            |           |            | I- 680                        Ramp+
            |           |            | I- 580                            +
            |           |            | State Hwy 13                  Ramp
    (2 rows)
Заметьте, что для одного столбца показываются две строки: одна соответствует полной иерархии наследования, построенной для таблицы road (inherited=t), и другая относится непосредственно к таблице road (inherited=f).

Объём информации, сохраняемой в pg_statistic командой ANALYZE, в частности максимальное число записей в массивах most_common_vals (самые популярные значения) и histogram_bounds (границы гистограмм) для каждого столбца, можно ограничить на уровне столбцов с помощью команды ALTER TABLE SET STATISTICS или глобально, установив параметр конфигурации default_statistics_target. В настоящее время ограничение по умолчанию равно 100 записям. Увеличивая этот предел, можно увеличить точность оценок планировщика, особенно для столбцов с нерегулярным распределением данных, ценой большего объёма pg_statistic и, возможно, увеличения времени расчёта этой статистики. И напротив, для столбцов с простым распределением данных может быть достаточно меньшего предела.

Подробнее использование статистики планировщиком описывается в Главе 67.

### 14.2.2. Расширенная статистика
Часто наблюдается картина, когда медленное выполнение запросов объясняется плохим выбором плана из-за того, что несколько столбцов, фигурирующих в условиях запроса, оказываются связанными. Обычно планировщик полагает, что несколько условий не зависят друг от друга, а это предположение оказывается неверным, когда значения этих столбцов коррелируют. Обычная статистика, которая по природе своей строится по отдельным столбцам, не может выявить корреляции между столбцами. Однако Postgres Pro имеет возможность вычислять многовариантную статистику, которая может собирать необходимую для этого информацию.

Так как число возможных комбинаций столбцов очень велико, автоматически вычислять многовариантную статистику непрактично. Вместо этого можно создать объекты расширенной статистики, чаще называемые просто объектами статистики, чтобы сервер собирал статистику по некоторым наборам столбцов, представляющим интерес.

Объекты статистики создаются командой CREATE STATISTICS. При создании такого объекта просто добавляется запись в каталоге, выражающая востребованность этой статистики. Собственно сбор данных выполняется процедурой ANALYZE (запускаемой вручную или автоматически в фоновом процессе). Изучить собранные значения можно в каталоге pg_statistic_ext_data.

Команда ANALYZE вычисляет расширенную статистику по той же выборке строк таблицы, которая используется и для вычисления обычной статистики по отдельным столбцам. Так как размер выборки увеличивается с увеличением целевого ограничения статистики для таблицы или любых её столбцов (как описано в предыдущем разделе), при большем целевом ограничении обычно получается более точная расширенная статистика, но и времени на её вычисление требуется больше.

В следующих подразделах описываются виды расширенной статистики, поддерживаемые в настоящее время.

#### 14.2.2.1. Функциональные зависимости
Простейший вид расширенной статистики отслеживает функциональные зависимости (это понятие используется в определении нормальных форм баз данных). Мы называем столбец b функционально зависимым от столбца a, если знания значения a достаточно для определения значения b, то есть не существует двух строк с одинаковыми значениями a, но разными значениями b. В полностью нормализованной базе данных функциональные зависимости должны существовать только в первичных ключах и суперключах. Однако на практике многие наборы данных не нормализуются полностью по разным причинам; например, денормализация часто производится намеренно по соображениям производительности.

Существование функциональных зависимостей напрямую влияет на точность оценок в определённых запросах. Если запрос содержит условия как по независимым, так и по зависимым столбцам, условия по зависимым столбцам дополнительно не сокращают размер результата. Однако без знания о функциональной зависимости планировщик запросов будет полагать, что все условия независимы, и недооценит размер результата.

Для информирования планировщика о функциональных зависимостях команда ANALYZE может собирать показатели зависимостей между столбцами. Оценить степень зависимости между всеми наборами столбцов обошлось бы непозволительно дорого, поэтому сбор данных ограничивается только теми группами столбцов, которые фигурируют вместе в объекте статистики, определённом со свойством dependencies. Во избежание ненужных издержек при выполнении ANALYZE и последующем планировании запросов статистику с dependencies рекомендуется создавать только для групп сильно коррелирующих столбцов.

Взгляните на пример сбора статистики функциональной зависимости:

    CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes;

    ANALYZE zipcodes;

    SELECT stxname, stxkeys, stxddependencies
    FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
    WHERE stxname = 'stts';
    stxname | stxkeys |             stxddependencies             
    ---------+---------+------------------------------------------
    stts    | 1 5     | {"1 => 5": 1.000000, "5 => 1": 0.423130}
    (1 row)
В показанном случае столбец 1 (код zip) полностью определяет столбец 5 (city), так что коэффициент равен 1.0, тогда как город (столбец city) определяет код ZIP только в 42% всех случаев, что означает, что многие города (58%) представлены несколькими кодами ZIP.

При вычислении избирательности запроса, в котором задействованы функционально зависимые столбцы, планировщик корректирует оценки избирательности по условиям, используя коэффициенты зависимостей, чтобы не допустить недооценки размера результата.

##### 14.2.2.1.1. Ограничения функциональных зависимостей
Функциональные зависимости в настоящее время применяются только при рассмотрении простых условий с равенствами, сравнивающих значения столбцов с константами. Они не используются для улучшения оценок при проверке равенства двух столбцов или сравнении столбца с выражением, а также в условиях с диапазоном, условиях LIKE или любых других видах условий.

Рассматривая функциональные зависимости, планировщик предполагает, что условия по задействованным столбцам совместимы и таким образом избыточны. Если условия несовместимы, правильной оценкой должен быть ноль строк, но эта возможность не рассматривается. Например, с таким запросом

    SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';
планировщик отбросит условие с city, так как оно не влияет на избирательность, что верно. Однако он сделает то же предположение и в таком случае:

    SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';
хотя на самом деле этому запросу будет удовлетворять ноль строк. Но статистика функциональной зависимости не даёт достаточно информации, чтобы прийти к такому заключению.

Во многих практических ситуациях это предположение обычно удовлетворяется; например, графический интерфейс приложения для последующего формирования запроса может не допускать выбор несовместимого сочетания города и кода ZIP. Но когда это не так, статистика функциональной зависимости может не подойти.

#### 14.2.2.2. Многовариантное число различных значений
Статистика по одному столбцу содержит число различных значений в каждом отдельном столбце. Оценки числа различных значений в сочетании нескольких столбцов (например, в GROUP BY a, b) часто оказываются ошибочными, когда планировщик имеет статистические данные только по отдельным столбцам, что приводит к выбору плохих планов.

Для улучшения таких оценок операция ANALYZE может собирать статистику по различным значениям для группы столбцов. Как и ранее, это непрактично делать для каждой возможной группы столбцов, так что данные собираются только по тем группам столбцов, которые указаны в определении объекта статистики, создаваемого со свойством ndistinct. Данные будут собираться по всем возможным сочетаниям из двух или нескольких столбцов из перечисленных в определении.

В продолжение предыдущего примера, количества различных значений в таблице ZIP-кодов могут выглядеть так:

    CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes;

    ANALYZE zipcodes;

    SELECT stxkeys AS k, stxdndistinct AS nd
    FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
    WHERE stxname = 'stts2';
    -[ RECORD 1 ]--------------------------------------------------------
    k  | 1 2 5
    nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178}
    (1 row)
Как видно, есть три комбинации столбцов, имеющих 33178 различных значений: код ZIP и штат; код ZIP и город; код ZIP, город и штат (то, что все эти числа равны, ожидаемый факт, так как сам по себе код ZIP в этой таблице уникален). С другой стороны, сочетание города и штата даёт только 27435 различных значений.

Объект статистики ndistinct рекомендуется создавать только для тех сочетаний столбцов, которые действительно используются при группировке, и только когда неправильная оценка числа групп может привести к выбору плохих планов. В противном случае усилия, потраченные на выполнение ANALYZE, будут напрасными.

#### 14.2.2.3. Многовариантные списки MCV
Ещё один тип статистики, сохраняемой для каждого столбца, представляют списки частых значений. Такие списки позволяют получать очень точную оценку для отдельных столбцов, но для запросов, содержащих условия с несколькими столбцами, полученная по ним оценка может быть значительно занижена.

Для улучшения таких оценок операция ANALYZE может собирать списки MCV по комбинациям столбцов. Подобно статистике функциональных зависимостей и различных значений, такую статистику непрактично собирать для каждой возможной группировки столбцов. В данном случае это ещё более актуально, так как списки MCV (в отличие от двух упомянутых статистик) содержат распространённые значения столбцов. Поэтому данные для них собираются только по тем группам столбцов, которые фигурируют в объекте статистики, определённом с указанием mcv.

В продолжение предыдущего примера, список MCV для таблицы ZIP-кодов может выглядеть следующим образом (в отличие от более простых типов статистики, для его анализа требуется применить функцию):

    CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcodes;

    ANALYZE zipcodes;

    SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),
                    pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';

    index |         values         | nulls | frequency | base_frequency 
    -------+------------------------+-------+-----------+----------------
        0 | {Washington, DC}       | {f,f} |  0.003467 |        2.7e-05
        1 | {Apo, AE}              | {f,f} |  0.003067 |        1.9e-05
        2 | {Houston, TX}          | {f,f} |  0.002167 |       0.000133
        3 | {El Paso, TX}          | {f,f} |     0.002 |       0.000113
        4 | {New York, NY}         | {f,f} |  0.001967 |       0.000114
        5 | {Atlanta, GA}          | {f,f} |  0.001633 |        3.3e-05
        6 | {Sacramento, CA}       | {f,f} |  0.001433 |        7.8e-05
        7 | {Miami, FL}            | {f,f} |    0.0014 |          6e-05
        8 | {Dallas, TX}           | {f,f} |  0.001367 |        8.8e-05
        9 | {Chicago, IL}          | {f,f} |  0.001333 |        5.1e-05
    ...
    (99 rows)
Выводимая информация показывает, что наиболее распространённую комбинацию города и штата образует Washington и DC, с частотой около 0.35% (в объёме выборки). Базовая частота этой комбинации (вычисленная из частот значений в отдельных столбцах) составляет всего 0.0027%, то есть эта оценка оказывается заниженной на два порядка.

Объекты статистики MCV рекомендуется создавать только для тех сочетаний столбцов, которые действительно используются в условиях вместе, и только когда неправильная оценка числа групп может привести к выбору плохих планов. В противном случае усилия, потраченные на выполнение ANALYZE и планирование, будут напрасными.

## 14.3. Управление планировщиком с помощью явных предложений JOIN
Поведением планировщика в некоторой степени можно управлять, используя явный синтаксис JOIN. Понять, когда и почему это бывает нужно, поможет небольшое введение.

В простом запросе с соединением, например таком:

    SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
планировщик может соединять данные таблицы в любом порядке. Например, он может разработать план, в котором сначала A соединяется с B по условию WHERE a.id = b.id, а затем C соединяется с получившейся таблицей по другому условию WHERE. Либо он может соединить B с C, а затем с A результатом соединения. Он также может соединить сначала A с C, а затем результат с B — но это будет не эффективно, так как ему придётся сформировать полное декартово произведение A и C из-за отсутствия в предложении WHERE условия, подходящего для оптимизации соединения. (В Postgres Pro исполнитель запросов может соединять только по две таблицы, поэтому для получения результата нужно выбрать один из этих способов.) При этом важно понимать, что все эти разные способы соединения дают одинаковые по смыслу результаты, но стоимость их может различаться многократно. Поэтому планировщик должен изучить их все и найти самый эффективный способ выполнения запроса.

Когда запрос включает только две или три таблицы, возможны всего несколько вариантов их соединения. Но их число растёт экспоненциально с увеличением числа задействованных таблиц. Если число таблиц больше десяти, уже практически невозможно выполнить полный перебор всех вариантов, и даже для шести или семи таблиц планирование может занять недопустимо много времени. Когда таблиц слишком много, планировщик Postgres Pro переключается с полного поиска на алгоритм генетического вероятностного поиска в ограниченном числе вариантов. (Порог для этого переключения задаётся параметром выполнения geqo_threshold.) Генетический поиск выполняется быстрее, но не гарантирует, что найденный план будет наилучшим.

Когда запрос включает внешние соединения, планировщик имеет меньше степеней свободы, чем с обычными (внутренними) соединениями. Например, рассмотрим запрос:

    SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
Хотя ограничения в этом запросе очень похожи на показанные в предыдущем примере, смысл его отличается, так как результирующая строка должна выдаваться для каждой строки A, даже если для неё не находится соответствия в соединении B и C. Таким образом, здесь планировщик не может выбирать порядок соединения: он должен соединить B с C, а затем соединить A с результатом. Соответственно, и план этого запроса построится быстрее, чем предыдущего. В других случаях планировщик сможет определить, что можно безопасно выбрать один из нескольких способов соединения. Например, для запроса:

    SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);
можно соединить A либо с B, либо с C. В настоящее время только FULL JOIN полностью ограничивает порядок соединения. На практике в большинстве запросов с LEFT JOIN и RIGHT JOIN порядком можно управлять в некоторой степени.

Синтаксис явного внутреннего соединения (INNER JOIN, CROSS JOIN или лаконичный JOIN) по смыслу равнозначен перечислению отношений в предложении FROM, так что он никак не ограничивает порядок соединений.

Хотя большинство видов JOIN не полностью ограничивают порядок соединения, в Postgres Pro можно принудить планировщик обрабатывать все предложения JOIN как ограничивающие этот порядок. Например, следующие три запроса логически равнозначны:

    SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
    SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
    SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
Но если мы укажем планировщику соблюдать порядок JOIN, на планирование второго и третьего уйдёт меньше времени. Когда речь идёт только о трёх таблицах, выигрыш будет незначительным, но для множества таблиц это может быть очень эффективно.

Чтобы планировщик соблюдал порядок внутреннего соединения, выраженный явно предложениями JOIN, нужно присвоить параметру выполнения join_collapse_limit значение 1. (Другие допустимые значения обсуждаются ниже.)

Чтобы сократить время поиска, необязательно полностью ограничивать порядок соединений, в JOIN можно соединять элементы как в обычном списке FROM. Например, рассмотрите следующий запрос:

    SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;
Если join_collapse_limit = 1, планировщик будет вынужден соединить A с B раньше, чем результат с другими таблицами, но в дальнейшем выборе вариантов он не ограничен. В данном примере число возможных вариантов соединения уменьшается в 5 раз.

Упрощать для планировщика задачу перебора вариантов таким способом — это полезный приём, помогающий не только выбрать сократить время планирования, но и подтолкнуть планировщик к хорошему плану. Если планировщик по умолчанию выбирает неудачный порядок соединения, вы можете заставить его выбрать лучший, применив синтаксис JOIN, конечно если вы сами его знаете. Эффект подобной оптимизации рекомендуется подтверждать экспериментально.

На время планирования влияет и другой, тесно связанный фактор — решение о включении подзапросов в родительский запрос. Пример такого запроса:

    SELECT *
    FROM x, y,
        (SELECT * FROM a, b, c WHERE something) AS ss
    WHERE somethingelse;
Такая же ситуация может возникнуть с представлением, содержащим соединение; вместо ссылки на это представление будет вставлено его выражение SELECT и в результате получится запрос, похожий на показанный выше. Обычно планировщик старается включить подзапрос в родительский запрос и получить таким образом:

    SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
Часто это позволяет построить лучший план, чем при планировании подзапросов по отдельности. (Например, внешние условия WHERE могут быть таковы, что при соединении сначала X с A будет исключено множество строк A, а значит формировать логический результат подзапроса полностью не потребуется.) Но в то же время тем самым мы увеличиваем время планирования; две задачи соединения трёх элементов мы заменяем одной с пятью элементами. Так как число вариантов увеличивается экспоненциально, сложность задачи увеличивается многократно. Планировщик пытается избежать проблем поиска с огромным числом вариантов, рассматривая подзапросы отдельно, если в предложении FROM родительского запроса оказывается больше чем from_collapse_limit элементов. Изменяя этот параметр выполнения, можно подобрать оптимальное соотношение времени планирования и качества плана.

Параметры from_collapse_limit и join_collapse_limit называются похоже, потому что они делают практически одно и то же: первый параметр определяет, когда планировщик будет «сносить» в предложение FROM подзапросы, а второй — явные соединения. Обычно join_collapse_limit устанавливается равным from_collapse_limit (чтобы явные соединения и подзапросы обрабатывались одинаково) или 1 (если требуется управлять порядком соединений). Но вы можете задать другие значения, чтобы добиться оптимального соотношения времени планирования и времени выполнения запросов.

## 14.4. Наполнение базы данных
14.4.1. Отключите автофиксацию транзакций
14.4.2. Используйте COPY
14.4.3. Удалите индексы
14.4.4. Удалите ограничения внешних ключей
14.4.5. Увеличьте maintenance_work_mem
14.4.6. Увеличьте max_wal_size
14.4.7. Отключите архивацию WAL и потоковую репликацию
14.4.8. Выполните в конце ANALYZE
14.4.9. Несколько замечаний относительно pg_dump
Довольно часто в начале или в процессе использования базы данных возникает необходимость загрузить в неё большой объём данных. В этом разделе приведены рекомендации, которые помогут сделать это максимально эффективно.

### 14.4.1. Отключите автофиксацию транзакций
Выполняя серию команд INSERT, выключите автофиксацию транзакций и зафиксируйте транзакцию только один раз в самом конце. (В обычном SQL это означает, что нужно выполнить BEGIN до, и COMMIT после этой серии. Некоторые клиентские библиотеки могут делать это автоматически, в таких случаях нужно убедиться, что это так.) Если вы будете фиксировать каждое добавление по отдельности, Postgres Pro придётся проделать много действий для каждой добавляемой строки. Выполнять все операции в одной транзакции хорошо ещё и потому, что в случае ошибки добавления одной из строк произойдёт откат к исходному состоянию и вы не окажетесь в сложной ситуации с частично загруженными данными.

### 14.4.2. Используйте COPY
Используйте COPY, чтобы загрузить все строки одной командой вместо серии INSERT. Команда COPY оптимизирована для загрузки большого количества строк; хотя она не так гибка, как INSERT, но при загрузке больших объёмов данных она влечёт гораздо меньше накладных расходов. Так как COPY — это одна команда, применяя её, нет необходимости отключать автофиксацию транзакций.

В случаях, когда COPY не подходит, может быть полезно создать подготовленный оператор INSERT с помощью PREPARE, а затем выполнять EXECUTE столько раз, сколько потребуется. Это позволит избежать накладных расходов, связанных с разбором и анализом каждой команды INSERT. В разных интерфейсах это может выглядеть по-разному; за подробностями обратитесь к описанию «подготовленных операторов» в документации конкретного интерфейса.

Заметьте, что с помощью COPY большое количество строк практически всегда загружается быстрее, чем с помощью INSERT, даже если используется PREPARE и серия операций добавления заключена в одну транзакцию.

COPY работает быстрее всего, если она выполняется в одной транзакции с командами CREATE TABLE или TRUNCATE. В таких случаях записывать WAL не нужно, так как в случае ошибки файлы, содержащие загружаемые данные, будут всё равно удалены. Однако это замечание справедливо только для несекционированных таблиц, когда параметр wal_level равен minimal, так как в противном случае все команды должны записывать свои изменения в WAL.

### 14.4.3. Удалите индексы
Если вы загружаете данные в только что созданную таблицу, быстрее всего будет загрузить данные с помощью COPY, а затем создать все необходимые для неё индексы. На создание индекса для уже существующих данных уйдёт меньше времени, чем на последовательное его обновление при добавлении каждой строки.

Если вы добавляете данные в существующую таблицу, может иметь смысл удалить индексы, загрузить таблицу, а затем пересоздать индексы. Конечно, при этом надо учитывать, что временное отсутствие индексов может отрицательно повлиять на скорость работы других пользователей. Кроме того, следует дважды подумать, прежде чем удалять уникальные индексы, так как без них соответствующие проверки ключей не будут выполняться.

### 14.4.4. Удалите ограничения внешних ключей
Как и с индексами, проверки, связанные с ограничениями внешних ключей, выгоднее выполнять «массово», а не для каждой строки в отдельности. Поэтому может быть полезно удалить ограничения внешних ключей, загрузить данные, а затем восстановить прежние ограничения. И в этом случае тоже приходится выбирать между скоростью загрузки данных и риском допустить ошибки в отсутствие ограничений.

Более того, когда вы загружаете данные в таблицу с существующими ограничениями внешнего ключа, для каждой новой строки добавляется запись в очередь событий триггера (так как именно срабатывающий триггер проверяет такие ограничения для строки). При загрузке многих миллионов строк очередь событий триггера может занять всю доступную память, что приведёт к недопустимой нагрузке на файл подкачки или даже к сбою команды. Таким образом, загружая большие объёмы данных, может быть не просто желательно, а необходимо удалять, а затем восстанавливать внешние ключи. Если же временное отключение этого ограничения неприемлемо, единственно возможным решением может быть разделение всей операции загрузки на меньшие транзакции.

### 14.4.5. Увеличьте maintenance_work_mem
Ускорить загрузку больших объёмов данных можно, увеличив параметр конфигурации maintenance_work_mem на время загрузки. Это приведёт к увеличению быстродействия CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. На скорость самой команды COPY это не повлияет, так что этот совет будет полезен, только если вы применяете какой-либо из двух вышеописанных приёмов.

### 14.4.6. Увеличьте max_wal_size
Также массовую загрузку данных можно ускорить, изменив на время загрузки параметр конфигурации max_wal_size. Загружая большие объёмы данных, Postgres Pro вынужден увеличивать частоту контрольных точек по сравнению с обычной (которая задаётся параметром checkpoint_timeout), а значит и чаще сбрасывать «грязные» страницы на диск. Временно увеличив max_wal_size, можно уменьшить частоту контрольных точек и связанных с ними операций ввода-вывода.

### 14.4.7. Отключите архивацию WAL и потоковую репликацию
Для загрузки больших объёмов данных в среде, где используется архивация WAL или потоковая репликация, быстрее будет сделать копию базы данных после загрузки данных, чем обрабатывать множество операций изменений в WAL. Чтобы отключить передачу изменений через WAL в процессе загрузки, отключите архивацию и потоковую репликацию, назначьте параметру wal_level значение minimal, archive_mode — off, а max_wal_senders — 0. Но имейте в виду, что изменённые параметры вступят в силу только после перезапуска сервера.

Это не только поможет сэкономить время архивации и передачи WAL, но и непосредственно ускорит некоторые команды, которые могут вовсе не использовать WAL, если wal_level равен minimal. (Они могут гарантировать безопасность при сбое, не записывая все изменения в WAL, а выполнив только fsync в конце операции, что будет гораздо дешевле.) Это относится к следующим командам:

- CREATE TABLE AS SELECT

- CREATE INDEX (и подобные команды, как например ALTER TABLE ADD PRIMARY KEY)

- ALTER TABLE SET TABLESPACE

- CLUSTER

- COPY FROM, когда целевая таблица была создана или опустошена ранее в той же транзакции

### 14.4.8. Выполните в конце ANALYZE
Всякий раз, когда распределение данных в таблице значительно меняется, настоятельно рекомендуется выполнять ANALYZE. Эта рекомендация касается и загрузки в таблицу большого объёма данных. Выполнив ANALYZE (или VACUUM ANALYZE), вы тем самым обновите статистику по данной таблице для планировщика. Когда планировщик не имеет статистики или она не соответствует действительности, он не сможет правильно планировать запросы, что приведёт к снижению быстродействия при работе с соответствующими таблицами. Заметьте, что если включён демон автоочистки, он может запускать ANALYZE автоматически; подробнее об этом можно узнать в Подразделе 23.1.3 и Подразделе 23.1.6.

### 14.4.9. Несколько замечаний относительно pg_dump
В скриптах загрузки данных, которые генерирует pg_dump, автоматически учитываются некоторые, но не все из этих рекомендаций. Чтобы загрузить данные, которые выгрузил pg_dump, максимально быстро, вам нужно будет выполнить некоторые дополнительные действия вручную. (Заметьте, что эти замечания относятся только к восстановлению данных, но не к выгрузке их. Следующие рекомендации применимы вне зависимости от того, загружается ли архивный файл pg_dump в psql или в pg_restore.)

По умолчанию pg_dump использует команду COPY и когда она выгружает полностью схему и данные, в сгенерированном скрипте она сначала предусмотрительно загружает данные, а потом создаёт индексы и внешние ключи. Так что в этом случае часть рекомендаций выполняется автоматически. Вам остаётся учесть только следующие:

- Установите подходящие (то есть превышающие обычные) значения для maintenance_work_mem и max_wal_size.

- Если вы используете архивацию WAL или потоковую репликацию, по возможности отключите их на время восстановления. Для этого перед загрузкой данных, присвойте параметру archive_mode значение off, wal_level — minimal, а max_wal_senders — 0. Закончив восстановление, верните их обычные значения и сделайте свежую базовую резервную копию.

- Поэкспериментируйте с режимами параллельного копирования и восстановления команд pg_dump и pg_restore, и подберите оптимальное число параллельных заданий. Параллельное копирование и восстановление данных, управляемое параметром -j, должно дать значительный выигрыш в скорости по сравнению с последовательным режимом.

- Если это возможно в вашей ситуации, восстановите все данные в рамках одной транзакции. Для этого передайте параметр -1 или --single-transaction команде psql или pg_restore. Но учтите, что в этом режиме даже незначительная ошибка приведёт к откату всех изменений и часы восстановления будут потрачены зря. В зависимости от того, насколько взаимосвязаны данные, предпочтительнее может быть вычистить их вручную. Команды COPY будут работать максимально быстро, когда они выполняются в одной транзакции и архивация WAL выключена.

- Если на сервере баз данных установлено несколько процессоров, полезным может оказаться параметр --jobs команды pg_restore. С его помощью можно выполнить загрузку данных и создание индексов параллельно.

- После загрузки данных запустите ANALYZE.

При выгрузке данных без схемы тоже используется команда COPY, но индексы, как обычно и внешние ключи, при этом не удаляются и не пересоздаются. [13] Поэтому, загружая только данные, вы сами должны решить, нужно ли для ускорения загрузки удалять и пересоздавать индексы и внешние ключи. При этом будет так же полезно увеличить параметр max_wal_size, но не maintenance_work_mem; его стоит менять, только если вы впоследствии пересоздаёте индексы и внешние ключи вручную. И не забудьте выполнить ANALYZE после; подробнее об этом можно узнать в Подразделе 23.1.3 и Подразделе 23.1.6.

## 14.5. Оптимизация, угрожающая стабильности
Стабильность — это свойство базы данных, гарантирующее, что результат зафиксированных транзакций будет сохранён даже в случае сбоя сервера или отключения питания. Однако обеспечивается стабильность за счёт значительной дополнительной нагрузки. Поэтому, если вы можете отказаться от такой гарантии, Postgres Pro можно ускорить ещё больше, применив следующие методы оптимизации. Кроме явно описанных исключений, даже с такими изменениями конфигурации при сбое программного ядра СУБД гарантия стабильности сохраняется; риск потери или разрушения данных возможен только в случае внезапной остановки операционной системы.

- Поместите каталог данных кластера БД в файловую систему, размещённую в памяти (т. е. в RAM-диск). Так вы исключите всю активность ввода/вывода, связанную с базой данных, если только размер базы данных не превышает объём свободной памяти (возможно, с учётом файла подкачки).

- Выключите fsync; сбрасывать данные на диск не нужно.

- Выключите synchronous_commit; нет необходимости принудительно записывать WAL на диск при фиксации каждой транзакции. Но учтите, это может привести к потере транзакций (хотя данные останутся согласованными) в случае сбоя базы данных.

- Выключите full_page_writes; защита от частичной записи страниц не нужна.

- Увеличьте max_wal_size и checkpoint_timeout; это уменьшит частоту контрольных точек, хотя объём /pg_wal при этом вырастет.

- Создавайте нежурналируемые таблицы для оптимизации записи в WAL (но учтите, что такие таблицы не защищены от сбоя).

